 <?php
require '../config.php';

$areaSize=0.0001; //11 meters
$distanceArea=($areaSize * 100000) * 1.1;
$maxAreaSize=1.0; //111km
$convertFactor=100000.0;

$action = $_GET["action"];
// Establish a MySQL connection and select our database using values contained in config.php.
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\\n", $mysqli->connect_error);
    exit();
}
$xmlstr = "<crombs></crombs>";
$xml = new SimpleXMLElement($xmlstr);

function getCrombs($xml,$mysqli){
  global $areaSize,$distanceArea,$maxAreaSize,$convertFactor;
  $lon = $_GET["lon"];
  $lat = $_GET["lat"];
  $startIndex = $_GET["index"];
  if (!empty($lon) && !empty($lat)){

    if (is_numeric($lon) && is_numeric($lat)){
      // convert lon and lat to usable 1.1 meters value
      // 5th digit is 1.1 meters
      $dbLon = round($lon * $convertFactor);
      $dbLat = round($lat * $convertFactor);
      // setting 100km square area to search...
      $dbMinLon = round(($lon-$areaSize)*$convertFactor);
      $dbMaxLon = round(($lon+$areaSize)*$convertFactor);
      $dbMinLat = round(($lat-$areaSize)*$convertFactor);
      $dbMaxLat = round(($lat+$areaSize)*$convertFactor);
      if(empty($startIndex)){
         $startIndex=0;
      }
      $query = "SELECT author,message,entrytime, glength(linestring(point(longitude,latitude),point(".$dbLon.",".$dbLat.")))*1.1 as dist FROM cromb where longitude between ".$dbMinLon." and ".$dbMaxLon." AND latitude between ".$dbMinLat." and ".$dbMaxLat." order by entrytime desc LIMIT 200";
       //echo $query; 
       if ($stmt = $mysqli->prepare($query)){
        $stmt->execute();
        $stmt->bind_result($author,$msg,$entrytime,$dist);
        while ($stmt->fetch()){
          $cromb = $xml->addChild("cromb",$msg);
          $cromb->addAttribute("author",$author);
          $cromb->addAttribute("date",$entrytime);
          $cromb->addAttribute("dist",$dist);
        }
        $stmt->close();
        putStatus($xml,"Success");	 
      }     
    }
  }
}

function getCrombsCount($xml,$mysqli){
  global $areaSize,$distanceArea,$maxAreaSize,$convertFactor;
  $lon = $_GET["lon"];
  $lat = $_GET["lat"];
  if (!empty($lon) && !empty($lat)){
    if (is_numeric($lon) && is_numeric($lat)){
      // convert lon and lat to usable 1.1 meters value
      // 5th digit is 1.1 meters
      $dbLon = round($lon * $convertFactor);
      $dbLat = round($lat * $convertFactor);
      // setting 10m square area to search...
      $dbMinLon = round(($lon-$areaSize)*$convertFactor);
      $dbMaxLon = round(($lon+$areaSize)*$convertFactor);
      $dbMinLat = round(($lat-$areaSize)*$convertFactor);
      $dbMaxLat = round(($lat+$areaSize)*$convertFactor);
      $query = "SELECT count(*) as c FROM cromb where longitude between ".$dbMinLon." and ".$dbMaxLon." and latitude between ".$dbMinLat." and ".$dbMaxLat;
       //echo $query; 
       if ($stmt = $mysqli->prepare($query)){
        $stmt->execute();
        $stmt->bind_result($count);
        while ($stmt->fetch()){
          $cromb = $xml->addChild("crombscan"," ");
          $cromb->addAttribute("count",$count);          
        }
        $stmt->close();
        putStatus($xml,"Success");	 
      }     
    }
  }
}
function getCrombsClosest($xml,$mysqli){
  global $areaSize,$distanceArea,$maxAreaSize,$convertFactor;
  $lon = $_GET["lon"];
  $lat = $_GET["lat"];
  if (!empty($lon) && !empty($lat)){
    if (is_numeric($lon) && is_numeric($lat)){
      // convert lon and lat to usable 1.1 meters value
      // 5th digit is 1.1 meters
      $dbLon = round($lon * $convertFactor);
      $dbLat = round($lat * $convertFactor);
      // setting 10m square area to search...
      $dbMinLon = round(($lon-$maxAreaSize)*$convertFactor);
      $dbMaxLon = round(($lon+$maxAreaSize)*$convertFactor);
      $dbMinLat = round(($lat-$maxAreaSize)*$convertFactor);
      $dbMaxLat = round(($lat+$maxAreaSize)*$convertFactor);
      $query = "SELECT dist from (SELECT glength(linestring(point(longitude,latitude),point(".$dbLon.",".$dbLat.")))*1.1 as dist FROM cromb where longitude between ".$dbMinLon." and ".$dbMaxLon." and latitude between ".$dbMinLat." and ".$dbMaxLat." order by dist) as q where dist > ".$distanceArea." limit 1";
      //echo $query; 
      if ($stmt = $mysqli->prepare($query)){
        $stmt->execute();
        $stmt->bind_result($dist);
        while ($stmt->fetch()){
          $cromb = $xml->addChild("crombsclosest"," ");
          $cromb->addAttribute("distance",$dist);
                    
        }
        $stmt->close();
        putStatus($xml,"Success");	 
      }     
    }
  }
}
function putCromb($xml,$mysqli){
  global $areaSize,$distanceArea,$maxAreaSize,$convertFactor;
  $lon = $_GET["lon"];
  $lat = $_GET["lat"];
  $author = $_GET["author"];
  $message = $_GET["message"];
  if (!empty($lon) && !empty($lat)){
    if (is_numeric($lon) && is_numeric($lat)){
      if (!empty($author) && !empty($message)){
        $dbLon = round($lon * $convertFactor);
        $dbLat = round($lat * $convertFactor);
        $insert = "INSERT INTO cromb (author, message, longitude,latitude) VALUES ('".str_replace("'","''",$author)."','".str_replace("'","''",$message)."',".$dbLon.",".$dbLat.")";
	mysqli_query($mysqli ,$insert);
        putStatus($xml,"Success");	
      }
    }
  }
}

function putStatus($xml,$status){
  global $areaSize,$distanceArea,$maxAreaSize,$convertFactor;
  $cromb = $xml->addChild("status","");
  $cromb->addAttribute("value",$status);          
}

switch($action){
  case "getcrombsclosest":
     getCrombsClosest($xml,$mysqli);
     break;
  case "scancromb":
     getCrombsCount($xml,$mysqli);
     break;
  case "getcrombs":
     getCrombs($xml,$mysqli);
     break;
   case "putcromb":
     putCromb($xml,$mysqli);
     break;
}
$mysqli->close();
printf($xml->asXML());
?>